package com.yuanhao.securitydemo.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Author Created By yuanhao wang
 * @Description excel文件数据 读取 写入
 * @Date Created in 2023/7/11 14:50
 * @Modified By
 */
public class ExcelUtil<T> {

    /**
     * @param file 传入(需要读取的文件)的文件
     * @return 返回的一个封装数据
     * @throws IOException 读取文件的一个io流异常
     */
    public static List<List<Object>> readExcel(File file) throws IOException{
        String fileName = file.getName();
        String suffix = fileName.lastIndexOf(".") == -1 ? "" :
                fileName.substring(fileName.lastIndexOf(".")+1);
        suffix = suffix.toUpperCase();
        if("XLS".equals(suffix)) {
            return read2003Excel(file);
        } if ("XLSX".equals(suffix)) {
            return read2007Excel(file);
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    private static List<List<Object>> read2003Excel(File file) throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = workbook.getSheetAt(0);
        Object value = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        int counter = 0;
        //getFirstRowNum 获取第一个有效的行数  getPhysicalNumcerOfRows 获取所有的行数(包括空白行)
        for (int i = sheet.getFirstRowNum(); counter < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) continue;
            else counter++;

            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if(cell == null) continue;
                //格式化Number String
                DecimalFormat df = new DecimalFormat("0");
                // 格式化日期字符串
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                // 格式化数字
                DecimalFormat nf = new DecimalFormat("0.00");

                switch (cell.getCellType()) {
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            value = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle()
                                .getDataFormatString())) {
                            value = nf.format(cell.getNumericCellValue());
                        } else {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell
                                    .getNumericCellValue()));
                        }
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case BLANK:
                        value = "";
                        break;
                    default:
                        value = cell.toString();
                }
                linked.add(value);
            }
            list.add(linked);
        }
        workbook.close();
        return list;
    }

    private static List<List<Object>> read2007Excel(File file) throws IOException{
        List<List<Object>> list = new LinkedList<List<Object>>();
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = xwb.getSheetAt(0);
        Object value = null;
        XSSFRow row = null;
        XSSFCell cell = null;
        int counter = 0;
        //getFirstRowNum 获取第一个有效的行数  getPhysicalNumcerOfRows 获取所有的行数(包括空白行)
        for (int i = sheet.getFirstRowNum(); counter < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) continue;
            else counter++;

            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if(cell == null) continue;
                //格式化Number String
                DecimalFormat df = new DecimalFormat("0");
                // 格式化日期字符串
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                // 格式化数字
                DecimalFormat nf = new DecimalFormat("0.00");

                switch (cell.getCellType()) {
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            value = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle()
                                .getDataFormatString())) {
                            value = nf.format(cell.getNumericCellValue());
                        } else {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell
                                    .getNumericCellValue()));
                        }
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case BLANK:
                        value = "";
                        break;
                    default:
                        value = cell.toString();
                }
                linked.add(value);
            }
            list.add(linked);
        }
        xwb.close();
        return list;
    }


    /**
     * @param rowNames 需要生成excel中列名字
     * @param name 需要生成excel中sheet的名字
     * @param data 需要填充到excel中的数据
     * @return 返回填充好的数据workbook
     */
    @SuppressWarnings("unchecked")
    public HSSFWorkbook excelOutPut(String[] rowNames, String name, List<T> data) {
        //创建工作铺对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建sheet对象
        HSSFSheet sheet = workbook.createSheet();
        //创建excel表的行
        HSSFRow row = sheet.createRow(0);
        //填充表头
        for(int i=0;i<rowNames.length;i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(rowNames[i]);
            cell.setCellValue(text);
        }
        //填充表数据
        Iterator<T> it = data.iterator();
        int index = 0;
        while(it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T)it.next();
            //利用反射 调取get方法  getDeclaredFields获取到类中所有的属性
            Field[] fileds = t.getClass().getDeclaredFields();
            for (int j = 0; j < fileds.length; j++) {
                HSSFCell dataCell = row.createCell(j);
                Field field = fileds[j];
                String fieldName = field.getName();
                //获取到属性拼接get方法
                String getMethodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
                try {
                    Class tcl = t.getClass();
                    Method getMethod = tcl.getMethod(getMethodName, null);
                    Object value = getMethod.invoke(t, null);
                    String textValue;
                    if (Objects.isNull(value)){
                        textValue = "";
                    } else {
                        textValue = value.toString();
                        if(textValue != null) {
                            Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                            Matcher matcher = p.matcher(textValue);
                            //判断是否是数字类型
                            if(matcher.matches()) {
                                dataCell.setCellValue(Double.parseDouble(textValue));
                            } else {
                                HSSFRichTextString richString = new HSSFRichTextString(
                                        textValue);
                                HSSFFont createFont = workbook.createFont();
                                createFont.setColor(IndexedColors.BLACK.getIndex());
                                richString.applyFont(createFont);
                                dataCell.setCellValue(richString);
                            }
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }

}

